package com.hctforgreen.greenservice.ctr;

import java.util.ArrayList;
import java.util.List;

import android.app.Activity;
import android.database.Cursor;

import com.hctforgreen.greenservice.db.DbExamAdapter;
import com.hctforgreen.greenservice.model.ExamPaperEntity;
import com.hctforgreen.greenservice.model.ExamSubEntity;
import com.hctforgreen.greenservice.model.ExamPaperEntity.PAPER_TYPE;
import com.hctforgreen.greenservice.service.HctApplication;
import com.hctforgreen.greenservice.utils.HctConstants;

/**
 * 考试草稿数据库操作类 db ctr
 * 
 * @author dhb
 * 
 */
public class DbExamController {

	private DbExamAdapter mDbAdapter;
	private Activity mActivity;

	public DbExamController(Activity activity) {
		mDbAdapter = ((HctApplication) activity.getApplication())
				.getDbExamAdapter();
		mActivity = activity;
	}

	/**
	 * 批量插入试题
	 * 
	 * @param dataList
	 */
	public void insert(List<ExamSubEntity> dataList) {
		mDbAdapter.getDatabase().beginTransaction();
		try {
			for (ExamSubEntity entity : dataList) {
				String sql = "insert into "
						+ HctConstants.DB_TABLE_NAME_EXAMSUB_LST + " (id,"
						+ "createTime," + "analyze," + "point," + "remark,"
						+ "name," + "answer," + "type," + "option,"
						+ "inputAnswer," + "paperId)" + " values "
						+ "(?,?,?,?,?,?,?,?,?,?,?)";
				mDbAdapter.execSQL(sql, new Object[] { entity.id,
						entity.createTime, entity.analyze, entity.point,
						entity.remark, entity.name, entity.answer, entity.type,
						entity.option, entity.inputAnswer, entity.paperId });
			}
			mDbAdapter.getDatabase().setTransactionSuccessful();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			mDbAdapter.getDatabase().endTransaction();
		}
	}

	/**
	 * 批量删除试题
	 * 
	 * @param dataList
	 */
	public void delete(List<ExamSubEntity> dataList) {

		mDbAdapter.getDatabase().beginTransaction();
		try {
			for (ExamSubEntity entity : dataList) {
				String sql = "delete from "
						+ HctConstants.DB_TABLE_NAME_EXAMSUB_LST
						+ " where id=?";
				mDbAdapter.execSQL(sql, new Object[] { entity.id });
			}
			mDbAdapter.getDatabase().setTransactionSuccessful();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			mDbAdapter.getDatabase().endTransaction();
		}
	}

	/**
	 * 查询试卷下的所有试题(草稿)
	 * 
	 * @param paperId
	 *            试卷id
	 * @return
	 */
	public List<ExamSubEntity> getExamSubListByPaperId(String paperId)
			throws Exception {
		List<ExamSubEntity> list = new ArrayList<ExamSubEntity>();
		String sql = "select * from " + HctConstants.DB_TABLE_NAME_EXAMSUB_LST
				+ " where paperId=?";
		Cursor cursor = mDbAdapter.rawQuery(sql, new String[] { paperId });
		for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
			ExamSubEntity entity = new ExamSubEntity();
			entity.id = cursor.getString(cursor.getColumnIndex("id"));
			entity.analyze = cursor.getString(cursor.getColumnIndex("analyze"));
			entity.answer = cursor.getString(cursor.getColumnIndex("answer"));
			entity.createTime = cursor.getString(cursor
					.getColumnIndex("createTime"));
			entity.inputAnswer = cursor.getString(cursor
					.getColumnIndex("inputAnswer"));
			entity.paperId = cursor.getString(cursor.getColumnIndex("paperId"));
			entity.point = Integer.valueOf(cursor.getString(cursor
					.getColumnIndex("point")));
			entity.name = cursor.getString(cursor.getColumnIndex("name"));
			entity.remark = cursor.getString(cursor.getColumnIndex("remark"));
			entity.type = cursor.getString(cursor.getColumnIndex("type"));
			entity.option = cursor.getString(cursor.getColumnIndex("option"));
			list.add(entity);
		}
		return list;
	}

	/**
	 * 插入或更新试题
	 * 
	 * @param subEntities
	 */
	public void saveOrUpdate(List<ExamSubEntity> subEntities) {
		mDbAdapter.getDatabase().beginTransaction();
		try {

			for (ExamSubEntity examSubEntity : subEntities) {
				if (isExist(examSubEntity)) {// 已存在数据库，更新操作
					update(examSubEntity);
				} else {
					save(examSubEntity);
				}
			}
			mDbAdapter.getDatabase().setTransactionSuccessful();
		} catch (Exception e) {
			e.printStackTrace();

		} finally {
			mDbAdapter.getDatabase().endTransaction();
		}

	}

	/**
	 * 插入一道试题
	 * 
	 * {@link ExamSubEntity}
	 * 
	 * @param ExamSubEntity
	 */
	public void save(ExamSubEntity entity) {
		try {
			String sql = "insert into "
					+ HctConstants.DB_TABLE_NAME_EXAMSUB_LST + " (id,"
					+ "createTime," + "analyze," + "point," + "remark,"
					+ "name," + "answer," + "type," + "option,"
					+ "inputAnswer," + "paperId)" + " values "
					+ "(?,?,?,?,?,?,?,?,?,?,?)";
			mDbAdapter.execSQL(sql, new Object[] { entity.id,
					entity.createTime, entity.analyze, entity.point,
					entity.remark, entity.name, entity.answer, entity.type,
					entity.option, entity.inputAnswer, entity.paperId });
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 更新草稿中的试题
	 * 
	 * @param entity
	 */
	public void update(ExamSubEntity entity) {
		String sql = "update " + HctConstants.DB_TABLE_NAME_EXAMSUB_LST
				+ " set name='" + entity.name + "'" + ",option='"
				+ entity.option + "'" + ",answer='" + entity.answer + "'"
				+ ",analyze='" + entity.analyze + "'" + ",point='"
				+ entity.point + "'" + ",type='" + entity.type + "'"
				+ ",inputAnswer='" + entity.inputAnswer + "'" + ",remark='"
				+ entity.remark + "'" + ",createTime='" + entity.createTime
				+ "'" + " where id='" + entity.id + "'";
		mDbAdapter.execSQL(sql);
	}

	/**
	 * 同一试卷 是否存在于数据库
	 * 
	 * @param ExamSubEntity
	 * @return
	 */
	public boolean isExist(ExamSubEntity entity) {
		String sql = "select count(*) from "
				+ HctConstants.DB_TABLE_NAME_EXAMSUB_LST + " where id='"
				+ entity.id + "'" + " and paperId='" + entity.paperId + "'";
		Cursor cursor = mDbAdapter.rawQuery(sql, null);
		cursor.moveToFirst();
		if (cursor.getInt(0) > 0) {
			cursor.close();
			return true;
		} else {
			cursor.close();
			return false;
		}
	}

	/**
	 * 保存或更新试卷
	 * 
	 * @param entity
	 */
	public void saveOrUpdate(ExamPaperEntity entity) {
		if (isExist(entity)) {// 数据库已有该试卷
			update(entity);// 更新
		} else {
			save(entity);// 保存
		}

	}

	/**
	 * 插入试卷
	 * 
	 * @param entity
	 */
	public void save(ExamPaperEntity entity) {
		try {
			String sql = "insert into "
					+ HctConstants.DB_TABLE_NAME_EXAM_PAPER_LST + " (id,"
					+ "createTime," + "name," + "remark," + "score,"
					+ "passScore," + "personId," + "isPass)" + " values "
					+ "(?,?,?,?,?,?,?,?)";
			mDbAdapter.execSQL(sql, new Object[] { entity.id,
					entity.createTime, entity.name, entity.remark,
					entity.score, entity.passScore, entity.personId,
					entity.isPass });
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	/**
	 * 更新试卷
	 * 
	 * @param entity
	 */
	public void update(ExamPaperEntity entity) {
		String sql = "update " + HctConstants.DB_TABLE_NAME_EXAM_PAPER_LST
				+ " set name='" + entity.name + "'" + ",createTime='"
				+ entity.createTime + "'" + ",isPass='" + entity.isPass + "'"
				+ ",remark='" + entity.remark + "'" + ",passScore='"
				+ entity.passScore + "'" + ",score='" + entity.score + "'"
				+ " where id='" + entity.id + "'";
		mDbAdapter.execSQL(sql);

	}

	/**
	 * 草稿试卷是否已存在
	 * 
	 * @param entity
	 * @return
	 */
	public boolean isExist(ExamPaperEntity entity) {

		String sql = "select count(*) from "
				+ HctConstants.DB_TABLE_NAME_EXAM_PAPER_LST + " where id='"
				+ entity.id + "'" + " and personId='" + entity.personId + "'";
		Cursor cursor = mDbAdapter.rawQuery(sql, null);
		cursor.moveToFirst();
		if (cursor.getInt(0) > 0) {
			cursor.close();
			return true;
		} else {
			cursor.close();
			return false;
		}
	}

	/**
	 * 删除试卷
	 * 
	 * @param entity
	 */
	public void delete(ExamPaperEntity entity) {
		String sql = "delete from " + HctConstants.DB_TABLE_NAME_EXAM_PAPER_LST
				+ " where id=?";
		mDbAdapter.execSQL(sql, new Object[] { entity.id });
	}

	/**
	 * 获取试卷草稿列表
	 * 
	 * @return
	 */
	public List<ExamPaperEntity> getExamPaperList(String personId) {
		List<ExamPaperEntity> list = new ArrayList<ExamPaperEntity>();
		String sql = "select * from "
				+ HctConstants.DB_TABLE_NAME_EXAM_PAPER_LST
				+ " where personId=?" + " order by createTime desc";
		Cursor cursor = mDbAdapter.rawQuery(sql, new String[] { personId });
		for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
			ExamPaperEntity entity = new ExamPaperEntity();
			entity.id = cursor.getString(cursor.getColumnIndex("id"));
			entity.createTime = cursor.getString(
					cursor.getColumnIndex("createTime")).substring(0, 16);
			entity.remark = cursor.getString(cursor.getColumnIndex("remark"));
			entity.isPass = Integer.valueOf(cursor.getString(cursor
					.getColumnIndex("isPass")));
			entity.name = cursor.getString(cursor.getColumnIndex("name"));
			entity.personId = cursor.getString(cursor
					.getColumnIndex("personId"));
			entity.score = Integer.valueOf(cursor.getString(cursor
					.getColumnIndex("score")));
			entity.passScore = Integer.valueOf(cursor.getString(cursor
					.getColumnIndex("passScore")));
			entity.paperType = PAPER_TYPE.TYPE_CACHE;
			list.add(entity);
		}
		return list;
	}

	/**
	 * 获取草稿数目
	 * 
	 * @param personId
	 * @return
	 */
	public int getExamPaperCount(String personId) {
		try {
			String sql = "select count(*) from "
					+ HctConstants.DB_TABLE_NAME_EXAM_PAPER_LST
					+ " where personId='" + personId + "'";
			Cursor cursor = mDbAdapter.rawQuery(sql, null);
			cursor.moveToFirst();
			return cursor.getInt(0);
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}
	}

}
